﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Flow_PostDocument]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Flow_PostDocument];
GO
CREATE PROCEDURE [dbo].[sproc_Flow_PostDocument]
    @StaffName nvarchar(300),
    @DocID int,    
    @ProjectID int =0 
AS

SET NOCOUNT ON
DECLARE @FlowRule int
DECLARE @Position_ID int
DECLARE @Super_Position_ID int

DECLARE @Project_ID INT
DECLARE @Parent_Project_ID int
DECLARE @PassNum int
DECLARE @StaffID int

DECLARE @Flow_ID int
DECLARE @Step_ID int

DECLARE @Local_Alert int
DECLARE @msgid int

DECLARE @SQL     nvarchar(3000)

SET @Project_ID     = @ProjectID
SET @PassNum         = 0
SET @Local_Alert    = 0
SET @SQL         = ''

--=======================================================================
--得到人员职位ID
SELECT
    @StaffID = a.staff_id,
    @Position_ID = b.Position_id
FROM
    dbo.uds_staff a,
    dbo.uds_staff_in_position b
WHERE
    a.Staff_ID = b.Staff_ID
    and a.staff_name = @StaffName;
IF ( @@rowcount <> 1 )
begin
    raiserror(N'The specified staff "%s" cannot be found.', 16, 1, @StaffName);
    return -1;
end;


--得到上级职位ID
SELECT     @Super_Position_ID     = Super_Position_ID
    FROM 
        uds_Position
    WHERE Position_id     = @Position_ID

--得到上级项目ID
SELECT     @Parent_Project_ID     = ClassParentID 
    FROM 
        uds_class
    WHERE     classid        = @Project_ID 
        and classid<>classparentid

--得到文档的当前流程ID和步骤ID
SELECT     @Flow_ID     = Flow_ID,
    @Step_ID    = Step_ID
    FROM 
        uds_flow_document
    WHERE     Doc_ID = @DocID 


--=======================================================================


--该步骤是否存在
IF EXISTS(SELECT 1 FROM uds_flow_step WHERE flow_id = @flow_id and step_id = @step_id)
BEGIN
    --得到当前环节流转规则和会签规则
    SELECT     @FlowRule = Flow_Rule,
        @PassNum = PassNum,    
        @Local_Alert = LocalAlert
        FROM 
            uds_flow_step
        WHERE     flow_id     = @flow_id 
            and step_id     = @step_id

    --汇签规则
    IF @PassNum <0
    BEGIN
        --删除真正的消息
        DELETE FROM UDS_SMS_MobileMsgSendBuffer        
            WHERE     msgid in (
                        SELECT     msgid 
                            FROM 
                                uds_flow_message
                            WHERE     doc_id         = @docid
                                and flow_id     = @flow_id
                                and step_id     = @step_id            
                                and staff_id     = @staffid        
                      )

        --删除当前步骤的提醒
        DELETE FROM uds_flow_message
            WHERE     doc_id         = @docid
                and flow_id     = @flow_id
                and step_id     = @step_id
                and staff_id     = @staffid
        
        --小于0为全体通过（全体指已经发送签收的人员）
        IF(SELECT COUNT(*) FROM uds_flow_status where doc_id =@docid )<>(SELECT COUNT(*) FROM uds_flow_postil WHERE doc_id =@docid and postil_type =1)
            RETURN -4        

    END
    ELSE
    BEGIN
        --需要汇签个数的
        IF @PassNum >0
        BEGIN
            --删除真正的消息
            DELETE FROM UDS_SMS_MobileMsgSendBuffer        
                WHERE     msgid in (
                            SELECT     msgid 
                                FROM 
                                    uds_flow_message
                                WHERE     doc_id         = @docid
                                    and flow_id     = @flow_id
                                    and step_id     = @step_id            
                                    and staff_id     = @staffid        
                          )

            --删除当前步骤的提醒
            DELETE FROM uds_flow_message
                WHERE     doc_id         = @docid
                    and flow_id     = @flow_id
                    and step_id     = @step_id
                    and staff_id    = @staffid

            UPDATE uds_flow_status 
                SET     status         = 2 
                WHERE     staff_id     = @staffid 
                    and doc_id     = @docid
            --小于汇签人数则不继续
            IF (SELECT COUNT(*) FROM uds_flow_postil WHERE doc_id =@docid and postil_type =1 and step_id = @step_id)<@PassNum
                RETURN -5
        END
        --等于0为不需要汇签的
    END

    --删除上一步骤的签收标志
    DELETE FROM 
            uds_flow_status 
        WHERE     doc_id         = @DocID
    --更新文档标志
    UPDATE uds_flow_document
        SET     doc_status     = 0,
            isrunning    = 1
        WHERE     doc_id         = @DocID
        
    --根据条件判断，得到流转到的ID    
    DECLARE @FieldName         nvarchar(10)
    DECLARE @Compare         nvarchar(10)
    DECLARE @Compare_ValueStr    nvarchar(300)
    DECLARE @Compare_Value         INT
    DECLARE @To_Step_id         INT

    DECLARE @Result_Step_ID        INT
    
    SET @To_Step_id     = -1        
    SET @Result_Step_ID     = -1

    DECLARE @CommandAnd    bit
    DECLARE @Successful    bit
    DECLARE @JumpFlowRule    int

    SET @CommandAnd = 0
    SET @Successful = 0
    SET @JumpFlowRule = -1
    
    CREATE TABLE #D (doc_id int)

    --进入条件跳转模块
    PRINT '进入条件跳转模块'
    DECLARE cur_Jump Cursor FOR
        SELECT  FieldName,compare,comparevalue,to_step_id,flow_rule
            FROM 
                uds_flow_jump
            WHERE     flow_id     = @flow_id
                and step_id    = @step_id
            ORDER BY priority
    OPEN cur_jump
    --================================================
    FETCH NEXT FROM cur_jump INTO @FieldName,@Compare,@Compare_Value,@To_Step_id,@JumpFlowRule

    IF @@FETCH_STATUS<>0 
        PRINT '没有条件'
    WHILE @@FETCH_STATUS = 0
    BEGIN
        --跳转到自己的为与条件判断开始
        IF @To_Step_id = @Step_id AND @CommandAnd = 0
        BEGIN
            SET @CommandAnd     = 1
            SET @Successful        = 1 
        END

        --多个与条件判断中
        IF @CommandAnd = 1
        BEGIN
            --第一个不为跳转到自己的为最后一个与条件判断
            IF @To_Step_id <> @Step_id 
            BEGIN
                --与条件判断结束
                SET @CommandAnd     = 0
            END

            IF @FieldName =  'caste'
            BEGIN
                SET @SQL = 'INSERT INTO #d SELECT 1 FROM uds_staff 
                        WHERE staff_id = ' + CONVERT(nvarchar(100),@StaffID)
                        + ' AND  CONVERT(INT,' + @FieldName + ')' + @Compare + CONVERT(nvarchar(100),@Compare_Value) 
            END
            ELSE            
                --条件判断
                SET @SQL = 'Insert INTO #d 
                        SELECT doc_id 
                            FROM 
                                uds_flow_style_data 
                            WHERE doc_id = ' + CONVERT(nvarchar(100),@DocID)
                            + ' AND  CONVERT(INT,' + @FieldName + ')' + @Compare + CONVERT(nvarchar(100),@Compare_Value) 
    
            --PRINT @SQL
            EXEC (@SQL)    
        
            IF NOT EXISTS(SELECT 1 FROM #D)
            BEGIN
                SET @Successful = 0                
            END    

            --删除条件判断成功部分
            DELETE FROM #d    
            
            --最后一个与条件判断
            IF @CommandAnd = 0
            BEGIN
                --判断都成功
                IF @Successful = 1
                BEGIN
                    SET @Result_Step_ID = @To_Step_id
                    BREAK
                END
            END 
        END
        --单个或条件
        ELSE
        BEGIN
            IF @FieldName =  'caste'
            BEGIN
                SET @SQL = 'INSERT INTO #d SELECT 1 FROM uds_staff 
                        WHERE staff_id = ' + CONVERT(nvarchar(100),@StaffID)
                        + ' AND  CONVERT(INT,' + @FieldName + ')' + @Compare + CONVERT(nvarchar(100),@Compare_Value) 
            END
            ELSE
                --设置查询语句
                SET @SQL = 'Insert INTO #d 
                        SELECT doc_id 
                            FROM 
                                uds_flow_style_data 
                            WHERE doc_id = ' + CONVERT(nvarchar(100),@DocID)
                            + ' AND  CONVERT(INT,' + @FieldName + ')' + @Compare + CONVERT(nvarchar(100),@Compare_Value) 
    
            --PRINT @SQL
            --执行语句
            EXEC (@SQL)    
            
            --条件判断成立
            IF EXISTS(SELECT 1 FROM #D)
            BEGIN
                SET @Result_Step_ID = @To_Step_id
                BREAK
            END
        END
        
        --下一条件
        FETCH NEXT FROM cur_jump INTO @FieldName,@Compare,@Compare_Value,@To_Step_id,@JumpFlowRule
    END
            
    --================================================
    CLOSE cur_jump
    DEALLOCATE cur_jump

    DROP TABLE #D
    
    --一个没找到，进入默认循环
    IF @Result_Step_ID <0 
    BEGIN
        IF EXISTS(SELECT 1 
                    FROM 
                        uds_flow_step 
                    WHERE     flow_id     = @flow_id
                        and step_id    = @step_id+1
              )
        BEGIN
            SET @Result_Step_ID = @step_id + 1
            PRINT '顺序跳转' + CONVERT(nvarchar(300),@Result_Step_ID)
        END
    END
    ELSE
    BEGIN
            --注意：@Result_Step_ID = 0 为条件跳转时，结束流程
            PRINT '条件跳转' + CONVERT(nvarchar(300),@Result_Step_ID)
            SET @FlowRule = @JumpFlowRule
    END
    
    --对于不是最后步骤的
    IF @Result_Step_ID>0
    BEGIN
        CREATE TABLE #staff (id int)
        
        --默认流传全体,流转规则是知流到下一步骤的人员规则
        IF @FlowRule = 0
        BEGIN
            INSERT INTO #staff 
                SELECT staff_id 
                    FROM     v_uds_flow_staff_in_step
                    WHERE     flow_id     = @flow_id
                        and step_id     = @Result_Step_ID

            PRINT '默认规则'
        END
        --按职位流转
        IF @FlowRule = 1
        BEGIN
            --挑选上级人员
            WHILE NOT EXISTS(SELECT 1 FROM #staff)
            BEGIN
                PRINT @Result_Step_ID
                --得到上级职位的所在成员在绑定组里成员
                INSERT INTO #staff
                    SELECT     a.staff_id 
                        FROM 
                            uds_staff_in_position a,
                            v_uds_flow_staff_in_step b
                        WHERE     a.staff_id         = b.staff_id 
                              and a.Position_id     = @Super_Position_ID
                             and b.flow_id         = @Flow_ID         
                            and b.step_id        = @Result_Step_ID

                PRINT @Super_Position_ID
                
                IF @Position_ID<>@Super_Position_ID
                BEGIN
                    SET @Position_ID         = @Super_Position_ID
                    SELECT     @Super_Position_ID     = Super_Position_ID    
                        FROM 
                            uds_Position
                        WHERE     Position_id     = @Position_ID                
                END 
                ELSE
                BEGIN    
                    PRINT '跳出循环'
                    BREAK
                END
                    
            END 
            PRINT '按职位:' + convert(nvarchar(100),@Super_Position_ID)
        END
        --按项目流转
        IF @FlowRule = 2
        BEGIN
            --如果是本组组长，选上级
            IF EXISTS(SELECT 1 FROM uds_staff_in_team WHERE team_id = @Project_ID AND staff_id = @staffid and member_type = 2)
            BEGIN
                SET @Project_ID = @Parent_Project_ID
            END

            --挑选上级人员
            WHILE NOT EXISTS(SELECT 1 FROM #staff)
            BEGIN            
                --得到上级项目成员在绑定组中成员                

                INSERT INTO #staff
                    SELECT     a.staff_id
                        FROM 
                            uds_staff_in_team a,
                            v_uds_flow_staff_in_step b
                        WHERE     a.staff_id     = b.staff_id 
                              and a.team_id     = @Project_ID
                              and b.flow_id     = @flow_id
                            and b.step_id     = @Result_Step_ID
                            and a.member_type = 2
 
                PRINT @Parent_Project_ID
                
                IF @Project_ID<>@Parent_Project_ID
                BEGIN
                    SET     @Project_ID         = @Parent_Project_ID
                    SELECT     @Parent_Project_ID     = ClassParentID    
                        FROM 
                            uds_class
                        WHERE     classid     = @Project_ID                
                END 
                ELSE
                BEGIN    
                    PRINT '跳出循环'
                    BREAK
                END
                    
            END 
            PRINT '按项目'
        END
    
        
        --发送签收标志
        IF EXISTS(SELECT 1 FROM #staff)
        BEGIN
            --发送站内消息
            IF @Local_Alert=1
            BEGIN

                INSERT INTO uds_sms_msg
                    (sender,content,sendtime,type)
                    VALUES(@staffname,'有新公文需要审批！',getdate(),1)
                SET @msgid = IDENT_CURRENT('uds_sms_msg')
                INSERT INTO uds_sms_receiver
                    (msgid,receiver,mobileno,type,isread)
                    SELECT     @msgid,
                        b.staff_name,
                        b.mobile,
                        1,0                        
                        FROM 
                            #staff a,
                            uds_staff b
                        WHERE 
                            a.id = b.staff_id

            END

            --删除真正的消息
            DELETE FROM UDS_SMS_MobileMsgSendBuffer        
                WHERE     msgid in (
                            SELECT     msgid 
                                FROM 
                                    uds_flow_message
                                WHERE     doc_id         = @docid
                          )

            --删除当前步骤的提醒
            DELETE FROM uds_flow_message
                WHERE doc_id = @docid

            --发送站外手机短信
            DECLARE @StendTime DATETIME
            DECLARE @RepeatTimes int
            DECLARE @RepeatPeriod int
            
            SELECT  @StendTime = DATEADD(ss,basehour,getdate()),
                    @RepeatTimes = Cyctimes -1 ,
                @RepeatPeriod = Period
                FROM 
                    uds_flow_step
                WHERE 
                    flow_id     = @flow_id
                    and step_id     = @step_id
            IF @RepeatTimes>=0
            BEGIN
                DECLARE @staffids nvarchar(3000)
                SET     @staffids = ''
                SELECT     @staffids = @staffids + ',' + CONVERT(nvarchar(30),id)
                    FROM #staff

                --发送流程提醒信息
                EXEC sproc_Flow_SMS_Send @docid,@flow_id,@Result_Step_ID,@staffids,'有新催办公文需要审批！',@StendTime,@RepeatTimes,@RepeatPeriod
            END

            --填写文档发送到当前步骤的人员
            INSERT INTO uds_flow_status
                SELECT     @DocID,ID,0
                    FROM 
                        #staff
            --备份当前步骤
            INSERT INTO uds_flow_path (doc_id,flow_id,step_id,staff_id,order_id)
                SELECT     @DocID,@flow_id,@Result_Step_ID,id,(SELECT COALESCE(MAX(order_id),0)+1 FROM uds_flow_path WHERE doc_id= @docid) AS order_id
                    FROM 
                        #staff
            --更新文档状态
            UPDATE uds_flow_document 
                SET step_id     = @Result_Step_ID,
                    obj_id    = @projectId,
                    obj_type     = @FlowRule
                WHERE doc_id     = @docid
            PRINT '文档已提交'        
            RETURN 0
        END
        ELSE
        BEGIN        
            IF @FlowRule = 0
            BEGIN
                PRINT '步骤没有成员'        
                RETURN -7
            END
            IF @FlowRule = 1 
            BEGIN
                PRINT '没有职位上级'        
                RETURN -2
            END
            IF @FlowRule = 2 
            BEGIN
                PRINT '没有项目上级'        
                RETURN -3
            END
            
        END

    END
    ELSE
    BEGIN

        --删除当前文档的真正提醒
        DELETE FROM UDS_SMS_MobileMsgSendBuffer
            WHERE msgid in (    
                    SELECT msgid 
                            FROM 
                                uds_flow_message
                            WHERE     doc_id = @docid
                    )

        --删除当前文档的提醒
        DELETE FROM uds_flow_message
            WHERE doc_id = @docid

        --给拟稿者发送消息
        INSERT INTO uds_sms_msg
            (sender,content,sendtime,type)
            SELECT @StaffName,'文档已经审批完成！',getdate(),1
                FROM 
                    uds_flow_document a,
                    uds_staff b                    
                WHERE     
                    b.staff_id = a.doc_builder_id
                    and a.doc_id = @docid

        SET @msgid = IDENT_CURRENT('uds_sms_msg')
        INSERT INTO uds_sms_receiver
            (msgid,receiver,mobileno,type,isread)
            SELECT     @msgid,
                b.staff_name,
                b.mobile,
                1,0                        
                FROM 
                    uds_flow_document a,
                    uds_staff b                    
                WHERE     
                    b.staff_id = a.doc_builder_id
                    and a.doc_id = @docid

        --删除所有的状态
        Delete FROM uds_flow_status
            WHERE doc_id     = @DocID

        --设置标记
        UPDATE uds_flow_document
            SET doc_status     = 3
            WHERE doc_id     = @DocID
        PRINT '最后环节，完成文档！'
        RETURN -1
    END
        
END 

SET NOCOUNT OFF